Snowflake SQLパフォーマンスチューニング 7 Tips #SnowflakeDB

「カスタマーストーリー」を支えるツール特集

#Snowflake

石川覚

2020.01.29

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

SnowflakeのSQLは、ANSI準拠のSQLであり、実際に使った印象も標準的なSQLです。本日は、Snowflakeをフルに活用するためのSQLパフォーマンスチューニングについて解説します。

まずはクエリ実行の流れを理解する

Snowflakeのクエリ実行の流れは「レコード操作 > グループ操作 > 結果出力」となります。そのため可能な限り最初の段階でスキャンのサイズ、結合対象のレコード数を減らした後、グループ操作をすることが最も重要です。

レコード操作は、グループ操作の前に実行されます。FROM句に指定したテーブルをWHERE句でフィルタした後、GROUP BY句やHAVING句で集約、最終的にSELECT句、DISTINCT句、ORDER BY句、LIMIT句にて結果出力します。クエリ実行の順は以下のとおりです。

この考えに基づき後述のTipsを紹介します。

Tips1: スキャンするバイト数を減らすにはLIMIT句ではなくWHERE句を使う

SnowflakeのクエリでLIMIT 10と指定した場合、すべてのレコードをスキャンした後に10レコードを返します。

SELECT * FROM LINEITEM LIMIT 10;

例えば、600万行以上のLINEITEMテーブルからザックリ10レコード確認したいだけでも対象のテーブルのすべてのレコードをスキャンします。すべてのスキャンを回避するにはフィルターの追加（WHERE句）によってデータをフィルタしてください。

Tips2: データのサンプルは[Preview Data]を使って参照する

テーブルデータの調査またはプレビューするには組み込み機能である、SnowflakeのWeb UIのテーブルの[Preview Data]を押して、データの参照します。Tips1の回避策としても有効です。

Tips3: 結果の並び替え（ORDER BY句）は最後に指定する

クエリがネストする場合、サブクエリでは並び替え（ORDER BY句）せずにトップレベルのSELECTでのみORDER BY句を指定してください。サブクエリ内で大きなテーブルのレコードの並び替え（ORDER BY句）せずに済むと、メモリ内で処理しきれずディスク書き出しによるパフォーマンスのボトルネックを回避できます。つまり、ORDER BYは最後に１つです。

Tips:4 一意キー（UNIQUE KEYS）によってテーブルを結合する

一意ではないキーでテーブルを結合すると、結果のデータ量が爆発的に増加（join explosion）する可能性があります。そのため、キーが一意であることや結合する前にテーブル間の関係を理解して、多対多の結合や意図しないクロス結合を回避するように心がけてください。

Tips5: Partition Pruningによる組み込みオプティマイザを活かす

Snowflakeは、マイクロパーティションの不要なデータスキャンの回避（プルーニング）によって、自動的にクエリパフォーマンスを最適化します。（特許取得済み）

WHERE句のカラムに基づく静的パーティションのプルーニング
クエリのJOINカラムに基づいた動的パーティションプルーニング

これらの仕組みを活かすには、クエリのできるだけ早い段階で適切なフィルターしてスキャン対象を削減します。また、Naturally clustered tables（ロードした順にクラスタリングしたテーブル）の場合、ロードした順序との相関が高い適切なpredicateカラム（日付カラムなど）をWHERE句に指定すると効果的です。

下記の例では、組み込みオプティマイザを活かすため、テーブルのクラスタリングオーダーのカラム（o_orderdate）でフィルタしています。

SELECT <items>
FROM order
WHERE
  o_orderdate >= to_date('1993-10-01') AND 
  o_orderdate < dateadd(month, 3, to_date('1993-10-01'))

マイクロパーティションとデータクラスタリングに関しては、以下のブログをご覧ください。

Snowflake マイクロパーティションとデータクラスタリングの解説 | Snowflake Advent Calendar 2019 #SnowflakeDB

Tips6: クエリ述語の関数利用はパフォーマンス低下を想定する

組み込み関数とユーザー定義関数（UDF）は非常に便利ですが、クエリ述語（WHERE句で指定する条件式）で使用するとパフォーマンスに影響を与える可能性があります。下記のWHERE句の例のようにカラム（l_extendpriceやo_totalprice）に対して関数（LOG関数）を適用しないと条件が一致しているか判断できないため、結果としてすべてのレコードのカラムに対して関数の実行が必要です。

下記の例では、クエリ述語の関数利用によって、プルーニングによる最適化ができずフルスキャンになり、かつ関数の実行するコストが生じるため、パフォーマンスが著しく低下します。

SELECT l_orderkey
FROM lineitem l, orders o
WHERE l_orderkey=o_orderkey AND
  LOG(10, l_extendprice) > 4.5 AND
  LOG(10, o_totalprice - l_tax) > 4.5

パフォーマンス低下を軽減する対策は、事前に関数の適用済みのカラムの追加や、一時テーブルで中間結果を中間テーブルに作成します。どちらかの方法、もしくは対策しないかの判断はフィルタの使用頻度やレコード数に応じて検討してください。

Tips7: 複数の重複排除（DISTINCT）よりもGROUP BYの方が速い

複数の重複排除（DISTINCT VALUES）する場合は、GROUP BY用いたほうが高速です。下記は、GROUP BYを用いた例です。

SELECT
   l_returnflag,
   l_linestatus,
   SUM(l_quantity)
FROM lineitem
GROUP BY 
   l_returnflag,
   l_linestatus;

補足：「DISTINCTをGROUP BYで書き換えるなんてオプティマイザの仕事では？」と思うかもしれません。しかし、この動作はSQLデータベースでよくあるTipsです。GROUP BYはデータをグループ化したのち新たに取り出して再集計するのに対して、DISTINCTは射影の過程で重複を排除します。つまり結果は同じだけどロジックが異なるので最適化の範囲ではないというわけです。

また、重複排除（DISTINCT）は一般にメモリを多く使用します。重複排除に指定する対象カラムの数が多いとメモリに収まらず、ディスクへの書き出し（Spilling）や大量データのネットワーク転送が生じた場合に、パフォーマンスに影響を及ぼす可能性があります。

まとめ

今回ご紹介したTipsは、SQLデータベースでよくあるTipsなのでざっと目を通していただければ、すぐに標準で備えた分析関数と組み合わせて高度なデータ分析が可能です。上記以外のパフォーマンスの懸念が生じた場合は、SnowflakeのWebUIからクエリプロファイルを使用してパフォーマンスの問題を認識できます。またの機会にご紹介したいと思います。